package top.sehnsucht.dao.impl;

import org.apache.log4j.Logger;
import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.utils.DBUtil;

import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/11/19
 */

public class ExcelDaoImpl implements ExcelDao {
    private Logger logger = Logger.getLogger(this.getClass());

    @Override
    public void createExcel(String name, ArrayList<String> list) throws SQLException {
        Connection conn;
        Statement stmt;
        conn = DBUtil.getConn();
        stmt = conn.createStatement();
        String sql = "create table " + name + "(id  int not null auto_increment primary key";
        for (String s : list) {
            sql += " , " + s + " varchar(100) ";
        }
        sql += " ) default charset = utf8;";
        stmt.executeUpdate(sql);
        logger.debug("创建表完成");
        stmt.close();
        DBUtil.closeConn(conn);
    }

    @Override
    public void addExcel(String name, ArrayList<String> list, ArrayList<String> data) throws Exception {
        Connection conn;
        PreparedStatement ps = null;
        conn = DBUtil.getConn();

        String sql = "insert into " + name + " ( ";
        for (int j = 0; j < list.size(); j++) {
            if (j == 0) {
                sql += list.get(j);
            } else {
                sql += ", " + list.get(j);
            }
        }
        sql += " ) values ( ";
        for (int j = 0; j < list.size(); j++) {
            if (j == 0) {
                sql += " ? ";
            } else {
                sql += " , ? ";
            }
        }
        sql += ");";
        ps = conn.prepareStatement(sql);
        for (int j = 0; j < data.size(); j++) {
            ps.setString(j + 1, data.get(j));
        }

        int count = ps.executeUpdate();
        if (count <= 0) {
            logger.error("插入失败！");
        }
        DBUtil.closeConn(conn);
        ps.close();
    }

    @Override
    public ArrayList<Map<String, String>> showTable() throws Exception {
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        ArrayList<Map<String, String>> arrs = new ArrayList<>();

        conn = DBUtil.getConn();
        String sql = "show tables;";

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery(sql);
        while (rs.next()) {
            Map<String, String> map = new HashMap();
            String username = rs.getString(1);
            map.put("name", username);
            arrs.add(map);
        }

        DBUtil.closeConn(conn);
        ps.close();

        return arrs;
    }

    @Override
    public ArrayList<ArrayList<String>> dataToExcel(String name) throws Exception {
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        ArrayList<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        conn = DBUtil.getConn();
        String sql = "select * from " + name;

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery(sql);
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int count = rsMetaData.getColumnCount();
        ArrayList<String> header = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            System.out.println(rsMetaData.getColumnName(i));
            header.add(rsMetaData.getColumnName(i));
        }
        list.add(header);
        while (rs.next()) {
            ArrayList<String> some = new ArrayList<String>();
            for (int i = 1; i <= count; i++) {
                if ("null".equals(rs.getString(i))) {
                    some.add("");
                } else {
                    some.add(rs.getString(i));
                }
            }
            list.add(some);
        }
        DBUtil.closeConn(conn);
        ps.close();

        return list;
    }
}
